import os
import sys
import win32com.client
import socket
import threading
import queue
from datetime import datetime

# 全局变量，用于存储总文件数和当前处理的文件数
total_files = 0
processed_files = 0

def process_excel_file(file_path, code_files):
    """
    处理单个Excel文件，导入新的VBA代码。

    :param file_path: Excel文件路径
    :param code_files: 包含新VBA代码文件的字典
    """
    global processed_files
    excel = win32com.client.Dispatch("Excel.Application")
    excel.Visible = False
    excel.DisplayAlerts = False

    try:
        workbook = excel.Workbooks.Open(file_path)
        vba_project = workbook.VBProject

        # 检查VBA项目是否受密码保护
        if vba_project.Protection == 1:  # 1 表示密码保护
            raise Exception("VBA工程受密码保护")

        # 删除所有VBA模块
        for component in list(vba_project.VBComponents):
            if component.Type == 1:  # 模块
                vba_project.VBComponents.Remove(component)

        # 导入新的代码文件
        for component_name, code_file_path in code_files.items():
            if os.path.exists(code_file_path):
                with open(code_file_path, 'r', encoding='utf-8') as file:
                    code = file.read()
                
                # 添加新的模块或更新现有模块
                if component_name == "ThisWorkbook":
                    component = vba_project.VBComponents("ThisWorkbook")
                    if component:
                        component.CodeModule.DeleteLines(1, component.CodeModule.CountOfLines)
                        component.CodeModule.AddFromString(code)
                elif component_name.startswith("Sheet"):
                    # 查找对应的Sheet组件
                    for sheet_component in vba_project.VBComponents:
                        if sheet_component.Type == 100 and sheet_component.Name == component_name:
                            sheet_component.CodeModule.DeleteLines(1, sheet_component.CodeModule.CountOfLines)
                            sheet_component.CodeModule.AddFromString(code)
                            break
                else:
                    # 添加标准模块
                    new_module = vba_project.VBComponents.Add(1)
                    new_module.Name = component_name
                    new_module.CodeModule.AddFromString(code)

        # 保存并关闭
        workbook.Save()
        workbook.Close()
    except Exception as e:
        record_protected_file(file_path)
    finally:
        excel.Quit()
        processed_files += 1
        update_progress()

def record_protected_file(file_path):
    """
    记录受密码保护的Excel文件路径到运行的EXE路径下的日志文件中。

    :param file_path: 受密码保护的Excel文件路径
    """
    # 获取运行的EXE文件所在目录
    exe_dir = os.path.dirname(os.path.abspath(sys.executable))
    log_filename = f"Protected.txt"
    log_path = os.path.join(exe_dir, log_filename)

    with open(log_path, 'a', encoding='utf-8') as log_file:
        log_file.write(f"受密码保护的文件: {file_path}\n")
    print(f"已记录受密码保护的文件: {file_path} 到 {log_path}")

def process_excel_files_in_folder(folder_path, code_files):
    """
    递归查找文件夹中的所有.xlsm文件并处理。

    :param folder_path: 文件夹路径
    :param code_files: 包含新VBA代码文件的字典
    """
    global total_files
    xlsm_files = []

    # 首先收集所有.xlsm文件
    for root, _, files in os.walk(folder_path):
        for filename in files:
            if filename.endswith(".xlsm"):
                xlsm_files.append(os.path.join(root, filename))

    total_files = len(xlsm_files)
    print(f"共找到 {total_files} 个.xlsm文件")

    # 处理每个文件
    for file_path in xlsm_files:
        process_excel_file(file_path, code_files)

def update_progress():
    """
    更新并显示处理进度。
    """
    global processed_files, total_files
    if total_files > 0:
        progress = (processed_files / total_files) * 100
        print(f"\r处理进度: {processed_files}/{total_files} ({progress:.1f}%)", end="")
        if processed_files == total_files:
            print("\n处理完成!")

def is_network_path(path):
    """
    判断路径是否为网络路径。

    :param path: 路径
    :return: 是否为网络路径
    """
    return path.startswith("\\\\")

def check_network_path(path, timeout=10):
    """
    检查网络路径是否可访问。

    :param path: 网络路径
    :param timeout: 超时时间（秒）
    :return: 是否可访问
    """
    if not is_network_path(path):
        return False

    try:
        # 获取网络路径的主机名
        host = path.split("\\")[2]
        # 创建一个队列用于存储结果
        result_queue = queue.Queue()

        # 定义一个函数用于检查主机是否可访问
        def check_host():
            try:
                socket.gethostbyname(host)
                result_queue.put(True)
            except socket.gaierror:
                result_queue.put(False)

        # 创建一个线程来检查主机
        thread = threading.Thread(target=check_host)
        thread.daemon = True
        thread.start()
        thread.join(timeout)

        # 如果线程仍在运行，说明超时
        if thread.is_alive():
            return False

        return result_queue.get() if not result_queue.empty() else False
    except Exception as e:
        print(f"检查网络路径 {path} 时出错: {str(e)}")
        return False

def main():
    # 获取Python代码文件所在目录
    current_dir = os.path.dirname(os.path.abspath(sys.argv[0]))

    # 示例代码文件路径
    code_files = {
        "ThisWorkbook": os.path.join(current_dir, "ThisWorkbook.bas"),  # ThisWorkbook代码文件路径
        "Sheet1": os.path.join(current_dir, "Sheet.bas"),  # Sheet代码文件路径
        "feng": os.path.join(current_dir, "feng.bas")  # 模块代码文件路径
    }

    # 检查是否有拖放的文件或文件夹
    if len(sys.argv) > 1:
        file_or_folder_path = sys.argv[1]
        if os.path.exists(file_or_folder_path):
            if os.path.isfile(file_or_folder_path) and file_or_folder_path.endswith(".xlsm"):
                process_excel_file(file_or_folder_path, code_files)
            elif os.path.isdir(file_or_folder_path):
                process_excel_files_in_folder(file_or_folder_path, code_files)
            else:
                print("请拖放一个有效的.xlsm文件或文件夹")
        else:
            # 检查是否为网络路径
            if is_network_path(file_or_folder_path):
                if check_network_path(file_or_folder_path):
                    if file_or_folder_path.endswith(".xlsm"):
                        process_excel_file(file_or_folder_path, code_files)
                    else:
                        process_excel_files_in_folder(file_or_folder_path, code_files)
                else:
                    print(f"无法访问网络路径 {file_or_folder_path}")
            else:
                print("路径无效")
    else:
        print("请拖放一个.xlsm文件或文件夹到此程序")

if __name__ == "__main__":
    main()
